package com.zhan.ielts.service.impl;

import com.aliyun.oss.ClientException;
import com.aliyun.oss.OSSException;
import com.google.common.io.Files;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.zhan.frame.core.dao.BaseDao;
import com.zhan.frame.core.dao.impl.BaseServiceImpl;
import com.zhan.frame.core.utils.*;
import com.zhan.ielts.dao.TopicDao;
import com.zhan.ielts.dao.TopicQADao;
import com.zhan.ielts.domain.Param;
import com.zhan.ielts.domain.Topic;
import com.zhan.ielts.domain.TopicQA;
import com.zhan.ielts.domain.excel.TopicPart1;
import com.zhan.ielts.domain.excel.TopicPart23;
import com.zhan.ielts.domain.vo.TopicQaVo;
import com.zhan.ielts.service.TopicService;
import com.zhan.ielts.utils.CustomPropertisUtils;
import com.zhan.ielts.utils.ExcelUtils;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileFilter;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;

@Service
public class TopicServiceImpl extends BaseServiceImpl<Topic> implements TopicService {
    private final String PART_1 = "part1";
    private final String PART_2 = "part2";
    private final String PART_3 = "part3";
    private final String PART_23 = "part23";
    @Autowired
    private TopicDao topicDao;
    @Autowired
    private TopicQADao topicQADao;

    @Override
    protected BaseDao<Topic> getBaseDao() {
        return topicDao;
    }

    @Override
    public boolean dealWithQuestion(MultipartFile questionFile) throws Exception {
        boolean flag = false;
        String tempFilePath = this.getClass().getClassLoader().getResource("").getPath();
        File file = new File("E:\\工作文档\\Test\\雅思口语机经2019年5-8月_0.xlsx");
        boolean isUpdate = false;
        try {
            String name = questionFile.getOriginalFilename();
            String appVersion = name.split("_")[1].replaceAll(".xlsx", "").trim();
//            file = new File(tempFilePath, name);
//            if (!file.exists()) {
//                file.mkdirs();
//            }
//            questionFile.transferTo(file);
            Map<String, Class> map = new HashMap<String, Class>();
            map.put(PART_1, TopicPart1.class);
            map.put(PART_23, TopicPart23.class);
            Map<String, List> returnMap = ExcelUtils.excelImportForTopic(map, file.getAbsolutePath());
            if (!returnMap.isEmpty()) {
                isUpdate = dealWithPart1(returnMap.get(PART_1), appVersion);
                boolean update = dealWithPart23(returnMap.get(PART_23), appVersion);
                if (!isUpdate) {
                    isUpdate = update;
                }
            }
            if (isUpdate) {
//                 buildQaJson2Aliyun();
            }
            flag = true;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (file != null) {
                file.delete();
            }
        }
        return flag;
    }

    @Override
    public boolean dealWithAudio(MultipartFile questionFile) throws Exception {
        boolean flag = true;
        //创建audio文件夹
        String tempFilePath = "E:\\工作文档\\Test\\unzip1\\audio\\";
//        String tempFilePath = this.getClass().getClassLoader().getResource("").getPath() + "audio" + File.separator;
        //获取附件文件名
        String name = questionFile.getOriginalFilename();
//        File file = new File(tempFilePath, name);
        File file = new File("E:\\工作文档\\Test\\part1录音.zip");
        File tempFilePathFile = new File(tempFilePath);
        try {
//            if (!tempFilePathFile.exists()) {
//                tempFilePathFile.mkdir();
//            }
//            if (!file.exists()) {
//                file.createNewFile();
//            }
            //建立文件audio下附件zip
//            questionFile.transferTo(file);

            String zipFilePath = tempFilePath + "ielts_audios_all_new.zip";
            //从ali上拉取总zip命名ielts_audios_all_new.zip
//            getIltesZipFromAli(tempFilePath, zipFilePath);

            //解压ielts_audios_all_new.zip
//            FileUtils.unzip(file.getAbsolutePath(), tempFilePath);
            this.buildQaJson();
            //获取audio下files的所有文件
            File[] arrFiles = new File(tempFilePath + "files").listFiles();
            for (File arrFile : arrFiles) {
                FileUtils.moveFile(arrFile.getAbsolutePath(), arrFile.getParentFile().getParent() + File.separator + arrFile.getName());
            }
            FileUtils.zipFile(tempFilePath, zipFilePath, "*.m4a,*/*.json", null);
            //以上是上处理总的音频 zipFilePath为生成的总的音频audios
            dealWithTopicAudio(new File(zipFilePath), new File(tempFilePath + "zip"));
        } catch (Exception e) {
            flag = false;
            e.printStackTrace();
        } finally {
            if (flag) {
                this.updateVersion(PART_1);
                this.updateVersion(PART_23);
            }
            FileUtils.removeAllFile(tempFilePath);
            tempFilePathFile.delete();
        }
        return flag;
    }



    private String[] getQuestions(String str) {
        List<String> list = new ArrayList<>();
        String[] arr = str.split("\n");
        for (String s : arr) {
            if (!StringUtils.isEmpty(s.trim())) {
                list.add(s);
            }
        }
        return list.toArray(new String[list.size()]);
    }

    private void dealWithTopicAudio(File total, File zipAudioFile) throws Exception {
        String aliyunFolder = CustomPropertisUtils.getProperties("aliyunTopicAudioPath");
        String cdnRoot = CustomPropertisUtils.getProperties("cdnRoot");
        //上传打包好的zip到阿里云
        AliyunOssUtil.uploadFile(total, aliyunFolder);
        //不知道干啥的冒出来的zip
        File[] files = zipAudioFile.listFiles();
        for (File file : files) {
            dealWithTopicAudio(file, cdnRoot, aliyunFolder);
        }

    }

    private void dealWithTopicAudio(File topicFile, String cdnRoot, String aliyunFolder) throws Exception {
        String fileName = topicFile.getName();
        String[] arr = fileName.split("_");
        Topic query = new Topic();
        query.setPart(arr[0].equals("p1") ? PART_1 : PART_23);
        query.setTopicCode(arr[1]);
        Topic topic = topicDao.selectOne(query);
        if (topic != null) {
            AliyunOssUtil.uploadFile(topicFile, aliyunFolder+ (StringUtils.isEmpty(topic.getTeacherId()) ? "" :  (topic.getTeacherId()+ "/")));
            String audio_url = cdnRoot + aliyunFolder + fileName;
            topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));
            topic.setAudioUrl(audio_url);
            updateTopic(query, topic);
        }
    }

    private boolean uploadFiles2Ali(List<String> files) {
        boolean isUpload = true;
        String aliyunFolder = CustomPropertisUtils.getProperties("aliyunTopicPath");
        try {
            for (String file : files) {
                this.uploadFile(file, aliyunFolder);
            }

        } catch (Exception e) {
            isUpload = false;
            e.printStackTrace();
        }
        return isUpload;
    }

    private void getIltesZipFromAli(String localPath, String zipFile) {
        String url = "http://cdn.tiku.zhan.com/ielts/topic/audio/ielts_audios_all_new.zip";
        NetUtil.doGetInpuetString(url, zipFile);
        FileUtils.unzip(zipFile, localPath);
    }

    @Override
    public List<String> buildQaJson() {
        //拉取所有qa 将qa放到vos里
        List<TopicQA> list = topicQADao.selectAll();
        List<TopicQaVo> vos = new ArrayList<>();
        List<String> paths = new ArrayList<>();
        if (!CollectionUtils.isEmpty(list)) {
            for (TopicQA topicQA : list) {
                TopicQaVo vo = new TopicQaVo();
                vo.setId(topicQA.getId());
                vo.setTopicCode(topicQA.getTopicCode());
                vo.setPartType(topicQA.getPartType());
                vo.setQuestion(topicQA.getQuestion());
                vo.setAnswer(topicQA.getAnswer());
                vo.setAudioName(topicQA.getAudioName());
                vo.setSort(topicQA.getSort());
                vos.add(vo);
            }
        }
        //临时Volist temps
        List<TopicQaVo> temps = new ArrayList<>();
        String classPath = this.getClass().getClassLoader().getResource("").getPath();
        //移除audio下qajson文件夹
        File audioRoot = new File(classPath + File.separator + "audio" + File.separator + "qajson");
        if (audioRoot.exists()) {
            FileUtils.removeAllFile(audioRoot.getAbsolutePath());
        }
        //新建文件夹
        audioRoot.mkdirs();
        //每500条新建一个json文件
        if (!CollectionUtils.isEmpty(vos)) {
            for (int i = 0; i < vos.size(); i++) {
                temps.add(vos.get(i));
                if ((i + 1) % 500 == 0) {
                    paths.add(buildQaJson(temps, audioRoot));
                    temps.clear();
                }
            }
            paths.add(buildQaJson(temps, audioRoot));
        }
        return paths;
    }

    @Override
    public void deleteTopicCascade(String id) {
        Topic topic = topicDao.selectById(id);
        if (null == topic) {
            return;
        }
        String topicCode = topic.getTopicCode();
        String topicPart = topic.getPart();
        topicDao.deleteById(id);
        try {
            TopicQA topicQA = new TopicQA();
            topicQA.setTopicCode(topicCode);
            if (PART_1.equals(topicPart)) {
                topicQA.setPartType(PART_1);
                topicQADao.delete(topicQA);
            } else if (PART_23.equals(topicPart)) {
                topicQA.setPartType(PART_2);
                topicQADao.delete(topicQA);
                topicQA.setPartType(PART_3);
                topicQADao.delete(topicQA);
            }
        } catch (Exception e) {
            throw e;
        }
    }

    @Override
    public void deleteTopicListCascade(String[] ids) {
        for (String id : ids) {
            try {
                this.deleteTopicCascade(id);
            } catch (Exception e) {
                throw e;
            }
        }
    }

    private String buildQaJson(List<TopicQaVo> temps, File audioRoot) {
        JSONArray arr = JSONArray.fromObject(temps);
        String jsonPath = audioRoot.getAbsoluteFile() + File.separator + UUIDUtils.random() + ".json";
        File file = new File(jsonPath);
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            Files.write(EncryptUtile.encrypt(arr.toString()).getBytes(), file);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return jsonPath;
    }

    private boolean dealWithPart23(List<TopicPart23> partls, String appVersion) throws Exception {
        boolean isUpdate = false;
        for (TopicPart23 part : partls) {
            if (StringUtils.isEmpty(part.getCode())) {
                break;
            }
            if ("N".equals(part.getIsUpdate())) {
                continue;
            }
            Topic topic = new Topic();
            topic.setTopicCode(part.getCode());
            topic.setPart(PART_23);
            Topic query = topicDao.selectOne(topic);
            if (query != null) {
                topic = query;
            }
            topic.setAppVersion(appVersion);
            topic.setTitle(part.getTitle());
            topic.setType(part.getType());
            topic.setFlag(part.getFlag());
            topic.setTeacherId(part.getOnLineAudio());
            topic.setFrequency(part.getFrequency());
            topic.setPriority(Integer.parseInt(part.getPriority() == null ? "0" : part.getPriority().replaceAll("(\\d+)\\.0", "$1")));
            topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));
            updateTopic(query, topic);
            insertPart23TopicQA(part);
            isUpdate = true;
        }
        if (isUpdate) {
            updateVersion(PART_23);
        }
        return isUpdate;
    }

    private void updateVersion(String type) {
        Param param = new Param();
        param.setParamName(type);
        param.setType("Topic");
        topicDao.updateParamVersion(param);
    }

    private void updateTopic(Topic query, Topic topic) {
        if (query == null) {
            topic.setRemoteVersion(0);
            topicDao.insert(topic);
        } else {
            topic.setRemoteVersion(topic.getRemoteVersion() + 1);
            topicDao.updateByIdSelective(topic);
        }
    }

    private void insertPart23TopicQA(TopicPart23 part) throws Exception {
        TopicQA topic2QA = new TopicQA();
        topic2QA.setQuestion(EncryptUtile.encrypt(part.getPart2()));
        if (!StringUtils.isEmpty(part.getPart2Answer())) {
            topic2QA.setAnswer(EncryptUtile.encrypt((part.getPart2Answer())));
        }

        topic2QA.setPartType(PART_2);
        topic2QA.setSort(1);
        topic2QA.setShowPoint(1);
        topic2QA.setTopicCode(part.getCode());
        topicQADao.insert(topic2QA);
        String[] part3Arr = getQuestions(part.getPart3());
        TopicQA query = new TopicQA();
        query.setTopicCode(part.getCode());
        query.setPartType(PART_3);
        query.setSort(part3Arr.length + 1);
        topicQADao.deleteByParas(query);
        Gson gson = new Gson();
        String answersJson = part.getPart3Answer();
        List<String> answerList = gson.fromJson(answersJson, new TypeToken<List<String>>() {
        }.getType());
        String[] answers = new String[answerList == null ? 0 : answerList.size()];
        if (null != answerList) {
            answerList.toArray(answers);
        }
        for (int i = 0; i < part3Arr.length; i++) {
            TopicQA topic3QA = new TopicQA();
            topic3QA.setQuestion(EncryptUtile.encrypt(part3Arr[i]));
            try {
                topic3QA.setAnswer(EncryptUtile.encrypt(answers[i]));
            }catch (Exception e){
                topic3QA.setAnswer("");
            }
            topic3QA.setPartType(PART_3);
            topic3QA.setShowPoint(1);
            topic3QA.setSort(i + 1);
            topic3QA.setTopicCode(part.getCode());
            topic3QA.setAudioName(EncryptUtile.encrypt("p3" + "_" + part.getCode() + "_" + (i + 1) + ".m4a"));
            topicQADao.insert(topic3QA);
        }
    }


    private String[] getAnswers(String str) {
        String arr[] = null;
        if (StringUtils.isEmpty(str)) {
            return new String[0];
        } else {
            arr = str.split("\nsplit\n");
        }
        return arr;
    }

    private boolean dealWithPart1(List<TopicPart1> part1s, String appVersion) throws Exception {
        boolean isUpdate = false;
        for (TopicPart1 part : part1s) {
            if (StringUtils.isEmpty(part.getCode())) {
                break;
            }
            if ("N".equals(part.getIsUpdate())) {
                continue;
            }
            Topic topic = new Topic();
            topic.setTopicCode(part.getCode());
            topic.setPart(PART_1);
            Topic query = topicDao.selectOne(topic);
            if (query != null) {
                topic = query;
            }
            topic.setAppVersion(appVersion);
            topic.setTitle(part.getTitle());
            topic.setFlag(part.getFlag());
            topic.setFrequency(part.getFrequency());
            topic.setTeacherId(part.getOnLineAudio());
            topic.setPriority(Integer.parseInt(part.getPriority() == null ? "0" : part.getPriority().replaceAll("(\\d+)\\.0", "$1")));
            topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));
            updateTopic(query, topic);
            insertPart1TopicQA(part);
            isUpdate = true;
        }
        if (isUpdate) {
            updateVersion(PART_1);
        }
        return isUpdate;
    }

    private void insertPart1TopicQA(TopicPart1 part) throws Exception {
        String[] partArr = getQuestions(part.getPart1());
        TopicQA query = new TopicQA();
        query.setTopicCode(part.getCode());
        query.setPartType(PART_1);
        query.setSort(partArr.length);
        // topicQADao.deleteByParas(query);
        //解析answerList to array
        Gson gson = new Gson();
        String answersJson = part.getAnswer();
        List<String> answerList = gson.fromJson(answersJson, new TypeToken<List<String>>() {
        }.getType());
        String[] answers = new String[answerList == null ? 0 : answerList.size()];
        if (null != answerList) {
            answerList.toArray(answers);
        }
        for (int i = 0; i < partArr.length; i++) {
            TopicQA topicQA = new TopicQA();
            topicQA.setQuestion(EncryptUtile.encrypt(partArr[i]));
            topicQA.setAnswer("");
            if (answers.length > 0 && !StringUtils.isEmpty(answers[i])) {
                topicQA.setAnswer(EncryptUtile.encrypt(answers[i]));
            }
            topicQA.setPartType(PART_1);
            topicQA.setShowPoint(1);
            topicQA.setTopicCode(part.getCode());
            topicQA.setAudioName(EncryptUtile.encrypt("p1" + "_" + part.getCode() + "_" + (i + 1) + ".m4a"));
            topicQA.setSort(i + 1);
            topicQADao.insert(topicQA);
        }
    }

    @Override
    public boolean uploadSingleFile(MultipartFile file, String fileFolder, String aliyunFolder) {
        try {
            String targetFolderStr = fileFolder + "topic/";
            File targetFile = saveFile(file, targetFolderStr);
            targetFile.renameTo(new File(targetFolderStr + file.getOriginalFilename()));
            this.uploadFile(targetFolderStr + file.getOriginalFilename(), aliyunFolder);

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean importTopic(MultipartFile file, String folder) {

        File targetFile;
        String cdnRoot = CustomPropertisUtils.getProperties("cdnRoot");
        List<Topic> topicList = new ArrayList<Topic>();

        try {
            targetFile = saveFile(file, folder + "topic/");
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(targetFile);

            XSSFSheet sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            XSSFRow row = sheet.getRow(0);

            // part1
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (StringUtils.isEmpty(row.getCell((short) 0))) {
                    break;
                }

                Topic topic = new Topic();
                topic.setTopicCode(String.valueOf(row.getCell((short) 0)));
                topic.setTitle(String.valueOf(row.getCell((short) 1)));
                topic.setPart("part1");
                topic.setQuestionUrl(cdnRoot + "ielts/topic/question/p1_" + topic.getTopicCode());
                topic.setAudioUrl(cdnRoot + "ielts/topic/audio/p1_" + topic.getTopicCode() + "_audio.zip");
                topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short) 4))));
                // topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short)
                // 0))));
                // topic.setType(String.valueOf(row.getCell((short) 8)));
                topic.setFlag(String.valueOf(row.getCell((short) 3)));
                topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));

                System.out.println(topic);
                topicList.add(topic);

            }

            // part23
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);

                if (StringUtils.isEmpty(row.getCell((short) 5))) {
                    break;
                }

                Topic topic = new Topic();
                topic.setTopicCode(String.valueOf(row.getCell((short) 5)));
                topic.setTitle(String.valueOf(row.getCell((short) 6)));
                topic.setPart("part23");
                topic.setQuestionUrl(cdnRoot + "ielts/topic/question/p23_" + topic.getTopicCode());
                topic.setAudioUrl(cdnRoot + "ielts/topic/audio/p3_" + topic.getTopicCode() + "_audio.zip");
                topic.setRemoteVersion(1);
                topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short) 11))));
                topic.setType(String.valueOf(row.getCell((short) 9)));
                topic.setFlag(String.valueOf(row.getCell((short) 10)));
                topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));
                topicList.add(topic);

            }
            topicDao.deleteAll();
            topicDao.insertInBatch(topicList);

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean handleQuestion(MultipartFile questionFile, String fileFolder, String aliyunFolder,
                                  boolean audioFlag) {
        // excel转json
        Map map = excel2Json(questionFile, fileFolder);
        boolean flag = (boolean) map.get("flag");
        // 上传question文件
        if (!flag) {
            return false;
        }

        flag = uploadQuestionFile(fileFolder, aliyunFolder, audioFlag);

        if (!flag) {
            return false;
        }
        flag = updateTopic((String) map.get("filepath"), fileFolder);
        return flag;
    }

    @Override
    public boolean handleAudio(MultipartFile audioFile, String fileFolder, String aliyunFolder) {
        try {
            File file = saveFile(audioFile, fileFolder + "topic/");
            String audioFilePath = fileFolder + "audio/";

            File audioFolder = new File(audioFilePath);
            if (audioFolder.exists()) {
                org.apache.commons.io.FileUtils.cleanDirectory(audioFolder);
            }

            // 解压到指定目录
            FileUtils.unzip(file.getAbsolutePath(), audioFilePath);

            // 重新压缩zip文件
            File ignoreFolder = new File(audioFilePath + "" +
                    "");
            if (ignoreFolder.exists()) {
                org.apache.commons.io.FileUtils.deleteDirectory(ignoreFolder);
            }

            String strTargetSourceFolder = fileFolder + "target/";
            File targetFile = new File(strTargetSourceFolder);
            if (!targetFile.exists()) {
                targetFile.mkdirs();

            } else {
                org.apache.commons.io.FileUtils.cleanDirectory(targetFile);
            }

            if (audioFolder.exists()) {
                File[] subaudioFolder = audioFolder.listFiles();
                for (int j = 0; j < subaudioFolder.length; j++) {
                    File[] audioFiles = subaudioFolder[j].listFiles();

                    String strTopicFolder = audioFiles[0].getName().substring(0, 6);

                    String folderName;
                    System.out.println(strTopicFolder + "--" + audioFiles.length);

                    for (int k = 0; k < audioFiles.length; k++) {

                        folderName = audioFiles[k].getName().substring(0, 6);
                        File subFolder = new File(strTargetSourceFolder + folderName);
                        if (!subFolder.exists()) {
                            subFolder.mkdir();
                        }
                        try {
                            org.apache.commons.io.FileUtils.copyFileToDirectory(audioFiles[k], subFolder);
                            org.apache.commons.io.FileUtils.copyFileToDirectory(audioFiles[k], new File(fileFolder
                                    + "total/"));
                        } catch (IOException e) {
                            e.printStackTrace();
                            return false;
                        }
                    }

                }
            }

            if (targetFile.exists()) {
                File[] subtargetFile = targetFile.listFiles();
                for (int j = 0; j < subtargetFile.length; j++) {
                    List filenames = Arrays.asList(subtargetFile[j].list());
                    FileUtils.zipFile(subtargetFile[j].getPath() + "/", filenames, new File(strTargetSourceFolder
                            + "zip/"
                            + String.valueOf(filenames.get(0)).substring(0, 6) + "_audio.zip"));

                }

            }

            File totalFolder = new File(fileFolder + "total/");
            File questionFolder = new File(fileFolder + "question");
            File[] subQuestionFolder = questionFolder.listFiles();
            for (int k = 0; k < subQuestionFolder.length; k++) {
                org.apache.commons.io.FileUtils.copyFileToDirectory(subQuestionFolder[k], totalFolder);
            }

            List filenames = Arrays.asList(totalFolder.list());
            FileUtils.zipFile(fileFolder + "total/", filenames, new File(strTargetSourceFolder
                    + "zip/ielts_audios_all.zip"));

            // 上传zip文件
            this.uploadFile(strTargetSourceFolder + "zip/", aliyunFolder + "audio/");
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public Map excel2Json(MultipartFile file, String folder) {
        Map map = new HashMap();
        boolean flag = false;
        String targetFileStr = null;

        try {

            File questionFolder = new File(folder + "question/");
            if (questionFolder.exists()) {
                org.apache.commons.io.FileUtils.cleanDirectory(questionFolder);
            }

            File targetFile = saveFile(file, folder + "topic/");
            targetFileStr = targetFile.getAbsolutePath();
            XSSFWorkbook wb = new XSSFWorkbook(targetFile);
            // 转换part1
            flag = excelToJsonP1(wb, "p1", folder + "question/");

            // 转换part23
            if (flag) {
                flag = excelToJsonP23(wb, "p23", folder + "question/");
            }

            // targetFile.delete();
            file = null;
            targetFile = null;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            flag = false;
        } catch (IOException e) {
            e.printStackTrace();
            flag = false;
        }
        map.put("flag", flag);
        map.put("filepath", targetFileStr);
        return map;
    }

    private boolean excelToJsonP1(XSSFWorkbook wb, String part, String folder) {
        XSSFSheet sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        XSSFRow row = sheet.getRow(0);

        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);

            String seqNo = String.valueOf(row.getCell((short) 0));
            String title = String.valueOf(row.getCell((short) 1));
            String part1 = String.valueOf(row.getCell((short) 2));

            if (StringUtils.isEmpty(row.getCell((short) 0))) {
                break;
            }

            // 整个json Object
            JSONObject jsonObject = new JSONObject();
            // 子json object
            JSONObject jsonSubObject = new JSONObject();
            // josn 数组
            JSONArray jsonArray = new JSONArray();

            String[] parts = part1.split("\n");
            for (int j = 0; j < parts.length; j++) {
                jsonSubObject.put("title", parts[j]);
                jsonSubObject.put("showPoint", 1);
                jsonSubObject.put("audioName", part + "_" + seqNo + "_" + (j + 1) + ".m4a");

                jsonArray.add(jsonSubObject);
            }

            jsonObject.put("part1", jsonArray);
            System.out.println(jsonObject.toString());

            try {
                FileUtils.createFile(folder + part + "_" + seqNo, EncryptUtile.encrypt(jsonObject.toString()));
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private boolean excelToJsonP23(XSSFWorkbook wb, String part, String folder) {
        XSSFSheet sheet = wb.getSheetAt(1);
        int rowNum = sheet.getLastRowNum();
        XSSFRow row = sheet.getRow(0);

        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);

            String seqNo = String.valueOf(row.getCell((short) 0));
            String title = String.valueOf(row.getCell((short) 1));
            String part2 = String.valueOf(row.getCell((short) 2));
            String part3 = String.valueOf(row.getCell((short) 3));

            if (StringUtils.isEmpty(row.getCell((short) 0))) {
                break;
            }

            // 整个json Object
            JSONObject jsonObject = new JSONObject();
            // 子json object
            JSONObject jsonSubObject2 = new JSONObject();
            JSONObject jsonSubObject3 = new JSONObject();
            // josn 数组
            JSONArray jsonArray2 = new JSONArray();
            JSONArray jsonArray3 = new JSONArray();

            // part2部分处理
            String[] parts2 = part2.split("\n");
            for (int s = 0; s < parts2.length; s++) {
                jsonSubObject2.put("title", parts2[s]);
                if (s == 0 || s == 1) {
                    jsonSubObject2.put("showPoint", 0);
                } else {
                    jsonSubObject2.put("showPoint", 1);
                }
                jsonArray2.add(jsonSubObject2);
            }

            // part3部分处理
            String[] parts3 = part3.split("\n");
            for (int t = 0; t < parts3.length; t++) {
                jsonSubObject3.put("title", parts3[t]);
                jsonSubObject3.put("showPoint", 1);
                jsonSubObject3.put("audioName", "p3" + "_" + seqNo + "_" + (t + 1) + ".m4a");

                jsonArray3.add(jsonSubObject3);
            }

            jsonObject.put("part2", jsonArray2);
            jsonObject.put("part3", jsonArray3);
            System.out.println(jsonObject.toString());

            try {

                FileUtils.createFile(folder + part + "_" + seqNo, EncryptUtile.encrypt(jsonObject.toString()));
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        return true;
    }

    private boolean updateTopic(String targetFilePath, String folder) {

        File targetFile;

        List<Topic> topicList = new ArrayList<Topic>();

        try {
            targetFile = new File(targetFilePath);
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(targetFile);

            XSSFSheet sheet = wb.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            XSSFRow row = sheet.getRow(0);

            // part1
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);

                if (StringUtils.isEmpty(row.getCell((short) 0))) {
                    break;
                }

                Topic topic = new Topic();
                topic.setTopicCode(String.valueOf(row.getCell((short) 0)));
                topic.setTitle(String.valueOf(row.getCell((short) 1)));
                topic.setPart("part1");
                topic.setQuestionUrl("http://cdn.tiku.zhan.com/" + folder + "question/p1_" + topic.getTopicCode());
                topic.setAudioUrl("http://cdn.tiku.zhan.com/" + folder + "audio/p1_" + topic.getTopicCode() + "_audio.zip");
//				topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short) 4)).split("\\.")[0]));
                // topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short)
                // 0))));
                // topic.setType(String.valueOf(row.getCell((short) 8)));
                topic.setFlag(String.valueOf(row.getCell((short) 3)));

                topic.setFrequency(String.valueOf(row.getCell((short) 4)));

                topic.setPriority(Integer.parseInt(String.valueOf(row.getCell((short) 5).getRawValue())));

                String updateFlag = String.valueOf(row.getCell((short) 6));
                Topic tmpTopic = topicDao.selectOne(new Topic(topic.getTopicCode(), topic.getPart()));
                //修改版本信息
                if (tmpTopic == null) {
                    topic.setRemoteVersion(1);
                } else if (updateFlag.equalsIgnoreCase("Y")) {
                    topic.setRemoteVersion(tmpTopic.getRemoteVersion() + 1);
                } else {
                    topic.setRemoteVersion(tmpTopic.getRemoteVersion());
                }

                topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));

                System.out.println(topic);
                topicList.add(topic);

            }

            // part23
            sheet = wb.getSheetAt(1);
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);

                if (row == null || StringUtils.isEmpty(String.valueOf(row.getCell((short) 0)))) {
                    break;
                }

                Topic topic = new Topic();
                topic.setTopicCode(String.valueOf(row.getCell((short) 0)));
                topic.setTitle(String.valueOf(row.getCell((short) 1)));
                topic.setPart("part23");
                topic.setQuestionUrl("http://cdn.tiku.zhan.com/" + folder + "question/p23_" + topic.getTopicCode());
                topic.setAudioUrl("http://cdn.tiku.zhan.com/" + folder + "audio/p3_" + topic.getTopicCode() + "_audio.zip");
//				topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short) 11)).split("\\.")[0]));
                // topic.setRemoteVersion(Integer.parseInt(String.valueOf(row.getCell((short)
                // 0))));
                topic.setType(String.valueOf(row.getCell((short) 4)));
                topic.setFlag(String.valueOf(row.getCell((short) 5)));

                topic.setFrequency(String.valueOf(row.getCell((short) 6)));
//				topic.setPriority(Integer.parseInt(String.valueOf(row.getCell((short) 7))));
                topic.setPriority(Integer.parseInt(String.valueOf(row.getCell((short) 7).getRawValue())));

                String updateFlag = String.valueOf(row.getCell((short) 8));
                Topic tmpTopic = topicDao.selectOne(new Topic(topic.getTopicCode(), topic.getPart()));
                //修改版本信息
                if (tmpTopic == null) {
                    topic.setRemoteVersion(1);
                } else if (updateFlag.equalsIgnoreCase("Y")) {
                    topic.setRemoteVersion(tmpTopic.getRemoteVersion() + 1);
                } else {
                    topic.setRemoteVersion(tmpTopic.getRemoteVersion());
                }

                topic.setUpdateTime(String.valueOf(System.currentTimeMillis()));

                System.out.println(topic);
                topicList.add(topic);

            }
            topicDao.deleteAll();

            if (topicList.size() > 0) {
                for (int s = 0; s < topicList.size(); s++) {
                    topicList.get(s).setUpdateTime(String.valueOf(System.currentTimeMillis()));
                    topicDao.insert(topicList.get(s));
                    Thread.sleep(1);
                }

            }
            // topicDao.insertInBatch(topicList);

            Param param = new Param();
            param.setType("topic");
            topicDao.updateParamVersion(param);

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return false;
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean uploadQuestionFile(String sourceFolder, String yunFilePath, boolean audioFlag) {
        boolean flag = true;
        try {
            flag = uploadFile(sourceFolder + "question/", yunFilePath + "question/");

            if (!flag)
                return false;

            // 生成并上传总包
            if (audioFlag) {
                File totalFolder = new File(sourceFolder + "total/");
                File questionFolder = new File(sourceFolder + "question/");

                if (!totalFolder.exists())
                    return true;

                File[] oldQuestionFiles = totalFolder.listFiles(new FileFilter() {
                    @Override
                    public boolean accept(File pathname) {
                        return pathname.getName().indexOf(".m4a") == -1;
                    }
                });
                for (int t = 0; t < oldQuestionFiles.length; t++) {
                    oldQuestionFiles[t].delete();
                }

                File[] subQuestionFolder = questionFolder.listFiles();
                for (int k = 0; k < subQuestionFolder.length; k++) {
                    org.apache.commons.io.FileUtils.copyFileToDirectory(subQuestionFolder[k], totalFolder);
                }

                List filenames = Arrays.asList(totalFolder.list());
                FileUtils.zipFile(sourceFolder + "total/", filenames, new File(sourceFolder
                        + "target/zip/ielts_audios_all.zip"));

                // 上传zip文件
                this.uploadFile(sourceFolder + "target/zip/ielts_audios_all.zip", yunFilePath + "audio/");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    private File saveFile(MultipartFile file, String path) throws IOException {
        String fileName = UUIDUtils.create() + file.getOriginalFilename();
        File targetFile = new File(path, fileName);
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }
        // 保存
        file.transferTo(targetFile);
        return targetFile;
    }

    public boolean uploadFile(String sourceFolder, String yunFilePath) {
        boolean flag = true;
        try {
            File folder = new File(sourceFolder);
            if (folder.isDirectory()) {
                File[] files = folder.listFiles();

                if (files != null && files.length > 0) {
                    for (int i = 0; i < files.length; i++) {
                        AliyunOssUtil.uploadFile(files[i], yunFilePath);
                    }
                }
            } else if (folder.isFile()) {
                AliyunOssUtil.uploadFile(folder, yunFilePath);
            }
        } catch (OSSException e) {
            e.printStackTrace();
            flag = false;
        } catch (ClientException e) {
            e.printStackTrace();
            flag = false;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return flag;
    }

}
